记一次处理Postgres json字段的有趣历程

任务

1
2
3
前端根据IDs向数据库查询数据。
数据结构复杂，有null/array/object，不排除有其他形式的legacy数据。
array/object字段不一，path不一，可能存在某个key的value属于IDs。

任务被分割成前后端2部分，因为难点在数据库JSON的处理上。任务没有deadline的压力，这是一次学习和实践数据库的机会，选择前后端一起做。

最初的想法，利用Postgres的json处理函数将字段Id提取出来和目标IDs匹配。现实中，字段的形式和数据格式都过于复杂，难以（优雅地）组装在where语句。一筹莫展之际，发现一个捷径：

1
将json字段处理成text, 用text匹配IDs，绕过处理json.

类似于%like%， in (1,2,3), Postgres支持SIMILAR TO正则匹配。问题简化成: 使用正则表达式匹配文本字符串。

1
WHERE data::text SIMILAR TO %(id-1|id-2|id-3)%

扩展

虽然SIMILAR TO是现实问题的最优解，却始终不是正解。 同时留下疑问，json to text对查询性能有多少影响？ 原生jsonb方法效率怎么样？

这里实现一个包含100w条记录的table， 分别对比char column字段查询 && json 函数查询 && json to string 查询三种查询方式的效率。
官方文档提供了SQL/JSON Path Language, 更加高效的处理jsonb数据，但是额外地，需要将字段GIN indexes, 本次测试没有使用SQL/JSON Path Language， 选择更加通用的一般操作符.

git repo： https://github.com/pingfengafei/pstgres-json-efficiency
测试结果：https://github.com/pingfengafei/pstgres-json-efficiency/blob/master/log

测试结论：

1
2
3
1. 原生字段查询最快，大约是json查询的5倍效率，约是json to string的10倍。
2. json to string查询100w条记录，大约耗时2秒。
3. 尽量避免使用json/jsonb存储数据，必须时用jsonb + GIN index。